package cn.com.lms.business.exam.persistence.repository;

import cn.com.lms.business.exam.persistence.entity.ExerciseEmployee;

import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface ExerciseEmployeeRepository extends BaseRepository<ExerciseEmployee,Long> {

    /**
     * 查询练习人员列表
     * @param exerciseId 练习ID
     * @return
     */
    @Query(value = "select ee.id,de.name as department, su.cname,su.dept_id as deptId, su.job_type_name as jobTypeName ,ee.employee_id as  employeeId, ee.exercise_id as exerciseId," +
            " (select count(1) from bnz_exam_employee_exercise_subject es where es.exercise_id=ee.exercise_id and es.employee_id=ee.employee_id) finishSubjectCount, " +
            " (select count(1) from bnz_exam_employee_exercise_subject es left join bnz_exam_question_subject_answer sa on sa.subject_id=es.subject_id  where es.exercise_id=ee.exercise_id and es.employee_id=ee.employee_id and es.answer=sa.answer) correctCount ," +
            "(select max(sub.create_time)  from bnz_exam_employee_exercise_subject sub " +
            " where sub.exercise_id=ee.exercise_id and sub.employee_id=ee.employee_id) lastExerciseTime," +
            " FLOOR(((select count(1) from bnz_exam_employee_exercise_subject es2 where es2.exercise_id=ee.exercise_id and es2.employee_id=ee.employee_id)/(select count(0) from bnz_exam_question_subject qs2 where qs2.is_delete=0 and qs2.question_id in(select eq2.question_id from bnz_exam_exercise_question eq2 where eq2.exercise_id=ee.exercise_id )))*100) finishRate," +
            " FLOOR(((select count(1) from bnz_exam_employee_exercise_subject es left join bnz_exam_question_subject_answer sa on sa.subject_id=es.subject_id  where es.exercise_id=ee.exercise_id and es.employee_id=ee.employee_id and es.answer=sa.answer)/(select count(1) from bnz_exam_employee_exercise_subject es where es.exercise_id=ee.exercise_id and es.employee_id=ee.employee_id))*100 )correctRate " +
            " from bnz_exam_exercise_employee ee LEFT JOIN  bnz_sys_user su on ee.employee_id=su.id " +
            " LEFT JOIN bnz_sys_department de on de.id=su.dept_id " +
            " where ee.is_delete=0 and  ee.exercise_id=:exerciseId  ", nativeQuery = true)
    List<Map> findEmployeeByExerciseId(@Param(value = "exerciseId") Long exerciseId);

    @Transactional
    @Modifying
    @Query(value = "delete from bnz_exam_exercise_employee where exercise_id=?1", nativeQuery = true)
    public int deleteByExerciseId(Long exerciseId);
}
